package com.eascs.web.o2o.web.controller;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Arrays;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.eascs.app.dao.PageResult;
import com.eascs.app.exception.ControllerException;
import com.eascs.common.doc.excel.ExcelApiHelper;
import com.eascs.common.exception.RuntimeIOException;
import com.eascs.common.util.IoUtil;
import com.eascs.common.util.StringUtil;
import com.eascs.common.util.UrlUtil;
import com.eascs.crm.common.entity.CustomerManager;
import com.eascs.crm.common.entity.CustomerShopInfoEntity;
import com.eascs.crm.common.vo.CustomerView;
import com.eascs.crm.common.vo.QueryCustomerVO;
import com.eascs.site.page.PaginationInfo;
import com.eascs.site.view.BaseRestResult;
import com.eascs.site.view.ViewContext;
import com.eascs.site.view.button.AjaxButton;
import com.eascs.site.view.button.Button;
import com.eascs.site.view.button.OpenButton;
import com.eascs.tag.MenuTag;
import com.eascs.web.o2o.web.common.BaseMasterViewController;
import com.eascs.web.o2o.web.entity.O2oPlatformRegional;
import com.eascs.web.o2o.web.form.ChannelBusStatisForm;
import com.eascs.web.o2o.web.service.BuildExcelService;
import com.eascs.web.o2o.web.service.ChannelBusStatisService;
import com.eascs.web.o2o.web.service.ProvinceNameData;
import com.eascs.web.o2o.web.util.BankRowWriteHandler;
import com.eascs.web.sso.NoPermissionException;

@Controller
@RequestMapping("/channel")
public class ChannelBusStatisController extends BaseMasterViewController {

	@Override
	public void preHandle() {
		ViewContext.put(MenuTag.CURRENT_MENU_CODE, "channel_bus");
	}

	@Autowired
	ChannelBusStatisService channelBusStatisService;
	
	@Autowired
	ProvinceNameData provinceNameData;

	@RequestMapping("/index")
	public String index(ChannelBusStatisForm entity) {
		ViewContext.put(MenuTag.CURRENT_MENU_CODE, "channel_bus");
		String o2ourl = "/restChannel/o2o";

		if (entity != null) {
			String billCode = entity.getBillCode() == null ? "" : entity.getBillCode();
			String loanType = entity.getLoanType() == null ? "" : entity.getLoanType();
			String partnerName = entity.getPartnerName() == null ? "" : entity.getPartnerName();
			String partnerType = entity.getPartnerType() == null ? "" : entity.getPartnerType();
			String loanerName = entity.getLoanerName() == null ? "" : entity.getLoanerName();
			String loanDate = entity.getLoanDate()== null ? "" : entity.getLoanDate();
			String loanDateEnd = entity.getLoanDateEnd()== null ? "" : entity.getLoanDateEnd();
			o2ourl = UrlUtil.appendUrl(o2ourl, "billCode", billCode);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanType", loanType);
			o2ourl = UrlUtil.appendUrl(o2ourl, "partnerName", partnerName);
			o2ourl = UrlUtil.appendUrl(o2ourl, "partnerType", partnerType);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanerName", loanerName);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanDate", loanDate);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateEnd", loanDateEnd);
			o2ourl = o2ourl + "&pageIndex=";
		} else {
			o2ourl = o2ourl + "?pageIndex=";
		}
		ViewContext.put("o2ourl", o2ourl);
		ViewContext.put("vo", entity);
		return "report/channelBusStatis";
	}

	@RequestMapping("/download")
	public void downloadTeam(ChannelBusStatisForm entity, HttpServletResponse httpServletResponse,
			HttpServletRequest request) {
		entity.setPageSize(Integer.MAX_VALUE);
		PageResult<Object[]> pageResult = channelBusStatisService.getChannelBusStatisByPage(entity);
		saveReport(pageResult.getData());
		String path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
		StringBuffer s = new StringBuffer();
		String outPath = s.append(path).append("/channel_file.xls").toString();
		OutputStream os = null;
		try {
			String outfile;
				outfile = "渠道业务统计.xls";
			String userAgent = request.getHeader("User-Agent");
			byte[] bytes = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
			outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
			File f = new File(outPath);
			if (!f.exists())
				throw new RuntimeIOException("file not found");
			os = httpServletResponse.getOutputStream();
			httpServletResponse.reset();
			httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
			httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			IoUtil.fileToOutputStream(f, byteArrayOutputStream);
			os.write(byteArrayOutputStream.toByteArray());
			os.flush();
		} catch (IOException e) {
			throw new RuntimeIOException(e);
		} finally {
			IoUtil.close(os);
		}
	}
	
	
	
	
	@RequestMapping("/index_1")
	public String index1(ChannelBusStatisForm entity) {
		ViewContext.put(MenuTag.CURRENT_MENU_CODE, "channel_bus1");
		String o2ourl = "/restChannel/o2o_1";

		if (entity != null) {
			String billCode = entity.getBillCode() == null ? "" : entity.getBillCode();
			String loanType = entity.getLoanType() == null ? "" : entity.getLoanType();
			String partnerName = entity.getPartnerName() == null ? "" : entity.getPartnerName();
			String partnerType = entity.getPartnerType() == null ? "" : entity.getPartnerType();
			String loanerName = entity.getLoanerName() == null ? "" : entity.getLoanerName();
			String loanDate = entity.getLoanDate()== null ? "" : entity.getLoanDate();
			String loanDateEnd = entity.getLoanDateEnd()== null ? "" : entity.getLoanDateEnd();
			o2ourl = UrlUtil.appendUrl(o2ourl, "billCode", billCode);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanType", loanType);
			o2ourl = UrlUtil.appendUrl(o2ourl, "partnerName", partnerName);
			o2ourl = UrlUtil.appendUrl(o2ourl, "partnerType", partnerType);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanerName", loanerName);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanDate", loanDate);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateEnd", loanDateEnd);
			o2ourl = o2ourl + "&pageIndex=";
		} else {
			o2ourl = o2ourl + "?pageIndex=";
		}
		ViewContext.put("o2ourl", o2ourl);
		ViewContext.put("vo", entity);
		return "report/channelBusStatis_1";
	}

	@RequestMapping("/download_1")
	public void downloadTeam1(ChannelBusStatisForm entity, HttpServletResponse httpServletResponse,
			HttpServletRequest request) {
		entity.setPageSize(Integer.MAX_VALUE);
		PageResult<Object[]> pageResult = channelBusStatisService.getChannelBusStatisByPage1(entity);
		saveReport(pageResult.getData());
		String path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
		StringBuffer s = new StringBuffer();
		String outPath = s.append(path).append("/channel_file.xls").toString();
		OutputStream os = null;
		try {
			String outfile;
				outfile = "渠道业务统计.xls";
			String userAgent = request.getHeader("User-Agent");
			byte[] bytes = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
			outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
			File f = new File(outPath);
			if (!f.exists())
				throw new RuntimeIOException("file not found");
			os = httpServletResponse.getOutputStream();
			httpServletResponse.reset();
			httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
			httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			IoUtil.fileToOutputStream(f, byteArrayOutputStream);
			os.write(byteArrayOutputStream.toByteArray());
			os.flush();
		} catch (IOException e) {
			throw new RuntimeIOException(e);
		} finally {
			IoUtil.close(os);
		}
	}

	// 先生成文件然后在读这个文件(省区查询报表)
	public String saveReport(List<Object[]> data) {
		// 模板
		String soucreFileName = "/excelTemplate/channel_bus_statis.xls";
		// 输出路径
		StringBuffer s = new StringBuffer();
		String path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();

		String outPath = s.append(path).append("/channel_file.xls").toString();

		// 包含的列
		int col = 13;
		// 需要统计的列
		int[] arr = {4};
		String title;

		title = "渠道业务统计";

		// 开始的表格，行，列
		int sheetIndex = 0;
		int rowIndex = 2;
		int columnIndex = 0;
		BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
				sheetIndex, rowIndex, columnIndex);

		// 生产excel
		write(soucreFileName, outPath, bankRowWriteHandler);

		return path + "/" + outPath;
	}

	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	//========================
	@RequestMapping("/brand_channel_list")
	public String brandStatis(ChannelBusStatisForm entity) {
		ViewContext.put(MenuTag.CURRENT_MENU_CODE, "brandChannelStatis");
		String o2ourl = "/restChannel/brand_channel";

		if (entity != null) {
			 String loanDateS = entity.getLoanDateS()== null ? "" : entity.getLoanDateS();
			 String loanDateE= entity.getLoanDateE()== null ? "" : entity.getLoanDateE();
			 String expireDateS= entity.getExpireDateS()== null ? "" : entity.getExpireDateS();
			 String expireDateE = entity.getExpireDateE()== null ? "" : entity.getExpireDateE();
			 String brandName= entity.getBrandName()== null ? "" : entity.getBrandName();
			 String loanStatus= entity.getLoanStatus()== null ? "" : entity.getLoanStatus();
			 String province= entity.getProvince()== null ? "" : entity.getProvince();
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateS", loanDateS);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateE", loanDateE);
			o2ourl = UrlUtil.appendUrl(o2ourl, "expireDateS", expireDateS);
			o2ourl = UrlUtil.appendUrl(o2ourl, "expireDateE", expireDateE);
			o2ourl = UrlUtil.appendUrl(o2ourl, "brandName", brandName);
			o2ourl = UrlUtil.appendUrl(o2ourl, "loanStatus", loanStatus);
			o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
			o2ourl = o2ourl + "&pageIndex=";
		} else {
			o2ourl = o2ourl + "?pageIndex=";
		}
		O2oPlatformRegional q= new O2oPlatformRegional();
		q.setProvinceCode("1");
		q.setProvinceName("总部");
		List<O2oPlatformRegional> proList = provinceNameData.getProvinceNameData();
		proList.add(0,q);
		ViewContext.put("proList", proList);
		ViewContext.put("o2ourl", o2ourl);
		ViewContext.put("vo", entity);
		
		return "report/brandChannelStatis";
	}
	
	
	@RequestMapping("/brand_download")
	public void downloadBrand(ChannelBusStatisForm entity, HttpServletResponse httpServletResponse,
			HttpServletRequest request) {
		entity.setPageSize(Integer.MAX_VALUE);
		PageResult<Object[]> pageResult = channelBusStatisService.getBrandChannelBusStatisByPage(entity);
		saveBrandReport(pageResult.getData(),entity.getProvince());
		String path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
		StringBuffer s = new StringBuffer();
		String outPath = s.append(path).append("/brand_channel_file.xls").toString();
		OutputStream os = null;
		try {
			String outfile;
				outfile = "按省区各品牌投放城市.xls";
			String userAgent = request.getHeader("User-Agent");
			byte[] bytes = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
			outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
			File f = new File(outPath);
			if (!f.exists())
				throw new RuntimeIOException("file not found");
			os = httpServletResponse.getOutputStream();
			httpServletResponse.reset();
			httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
			httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			IoUtil.fileToOutputStream(f, byteArrayOutputStream);
			os.write(byteArrayOutputStream.toByteArray());
			os.flush();
		} catch (IOException e) {
			throw new RuntimeIOException(e);
		} finally {
			IoUtil.close(os);
		}
	}

	// 先生成文件然后在读这个文件(省区查询报表)
	public String saveBrandReport(List<Object[]> data,String province) {
		// 模板
		String soucreFileName = "/excelTemplate/brand_channel_statis.xls";
		// 输出路径
		StringBuffer s = new StringBuffer();
		String path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();

		String outPath = s.append(path).append("/brand_channel_file.xls").toString();

		// 包含的列
		int col = 12;
		// 需要统计的列
		int[] arr = {4};
		String title;
		if(province==null||("").equals(province)){
			province="";
		}
		title = province+"省区统计品牌投放        单位万元";

		// 开始的表格，行，列
		int sheetIndex = 0;
		int rowIndex = 3;
		int columnIndex = 0;
		BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
				sheetIndex, rowIndex, columnIndex);

		// 生产excel
		write(soucreFileName, outPath, bankRowWriteHandler);

		return path + "/" + outPath;
	}
	
	
	
	//========================
		@RequestMapping("/view_loan_data")
		public String viewLoanData(ChannelBusStatisForm entity) {
			ViewContext.put(MenuTag.CURRENT_MENU_CODE, "viewLoanData");
			String o2ourl = "/restChannel/restViewLoanData";
			if (entity != null) {
				 String loanDateS = entity.getLoanDateS()== null ? "" : entity.getLoanDateS();
				 String loanDateE= entity.getLoanDateE()== null ? "" : entity.getLoanDateE();
				 String province= entity.getProvince()== null ? "" : entity.getProvince();
				 o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateS", loanDateS);
				 o2ourl = UrlUtil.appendUrl(o2ourl, "loanDateE", loanDateE);
				 o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
				 o2ourl = o2ourl + "&pageIndex=";
			} else {
				 o2ourl = o2ourl + "?pageIndex=";
			}
			O2oPlatformRegional q= new O2oPlatformRegional();
			q.setProvinceCode("1");
			q.setProvinceName("总部");
			List<O2oPlatformRegional> proList = provinceNameData.getProvinceNameData();
			proList.add(0,q);
			ViewContext.put("proList", proList);
			ViewContext.put("o2ourl", o2ourl);
			ViewContext.put("vo", entity);
			return "report/viewLoanData";
		}
		
		
		@RequestMapping("/viewLoanData_download")
		public void downloadViewLoanData(ChannelBusStatisForm entity, HttpServletResponse httpServletResponse,
				HttpServletRequest request) {
			entity.setPageSize(Integer.MAX_VALUE);
			PageResult<Object[]> pageResult = channelBusStatisService.getRestViewLoanDataByPage(entity);
			saveViewLoanDataReport(pageResult.getData(),entity.getProvince());
			String path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
			StringBuffer s = new StringBuffer();
			String outPath = s.append(path).append("/view_loan_data_file.xls").toString();
			OutputStream os = null;
			try {
				String outfile;
					outfile = "放款台账报表.xls";
				String userAgent = request.getHeader("User-Agent");
				byte[] bytes = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
				outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
				File f = new File(outPath);
				if (!f.exists())
					throw new RuntimeIOException("file not found");
				os = httpServletResponse.getOutputStream();
				httpServletResponse.reset();
				httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
				httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
				ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
				IoUtil.fileToOutputStream(f, byteArrayOutputStream);
				os.write(byteArrayOutputStream.toByteArray());
				os.flush();
			} catch (IOException e) {
				throw new RuntimeIOException(e);
			} finally {
				IoUtil.close(os);
			}
		}

		// 先生成文件然后在读这个文件(省区查询报表)
		public String saveViewLoanDataReport(List<Object[]> data,String province) {
			// 模板
			String soucreFileName = "/excelTemplate/view_loan_data.xls";
			// 输出路径
			StringBuffer s = new StringBuffer();
			String path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();

			String outPath = s.append(path).append("/view_loan_data_file.xls").toString();

			// 包含的列
			int col = 6;
			// 需要统计的列
			int[] arr = {3};
			String title="放款台账报表";
			

			// 开始的表格，行，列
			int sheetIndex = 0;
			int rowIndex = 2;
			int columnIndex = 0;
			BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
					sheetIndex, rowIndex, columnIndex);

			// 生产excel
			write(soucreFileName, outPath, bankRowWriteHandler);

			return path + "/" + outPath;
		}
		
		
		
		
	
	
	

	@RequestMapping("/ownChannelList")
	public String ownChannelList(ChannelBusStatisForm entity) {
		ViewContext.put(MenuTag.CURRENT_MENU_CODE, "ownChannelList");
		return listChannel(entity,"/restChannel/ownDetail","ownChannelList","ownChannelDownload","queryChannelForBill1","downloadChannelForBill1");
	}
	@RequestMapping("/commonChannelList")
	public String commonChannelList(ChannelBusStatisForm entity) {
		ViewContext.put(MenuTag.CURRENT_MENU_CODE, "commonChannelList");
		return listChannel(entity,"/restChannel/commonDetail","commonChannelList","commonChannelDownload","queryChannelForBill2","downloadChannelForBill2");
	}
	
	@RequestMapping("/ownChannelDownload")
	public void ownChannelDownload(ChannelBusStatisForm entity, HttpServletResponse httpServletResponse,
			HttpServletRequest request) {
		download(entity,httpServletResponse,request,"0");
	}
	
	@RequestMapping("/commonChannelDownload")
	public void commonChannelDownload(ChannelBusStatisForm entity, HttpServletResponse httpServletResponse,
			HttpServletRequest request) {
		download(entity,httpServletResponse,request,"1");
	}
	
	
	@RequestMapping(value = "/channelDetail")
	public String showCustomerDetailInfo(ChannelBusStatisForm entity) {
		//渠道信息
		List<Object[]> list = channelBusStatisService.getChannelObject(entity.getPartnerCode());
		if(list!=null&&list.size()>0){
			Object[] b = list.get(0);
			ViewContext.put("vo", b);
		}
		//渠道放款数据 
		entity.setPageSize(10);
		PageResult<Object[]> pageResult = channelBusStatisService.getChannelforBill(entity);
		String[] titles = {  "贷款单号", "借款人", "省区" ,"信贷经理","放款金额（万元）","放款日期","推荐类型","推荐人"};
		PaginationInfo paginationInfo = new PaginationInfo(pageResult, titles, "/channel/channelDetail?partnerCode="+entity.getPartnerCode()+"&pageIndex=");
		paginationInfo.setBuildPagingNavigation(true);
		paginationInfo.setFirstRenderColumnIndex(0);		
		paginationInfo.setBuildRowNumber(true);
		ViewContext.put("paginationInfo", paginationInfo);
		BaseRestResult.refresh();
		return "report/channelDetail";
	}
	
	
	
	
	
	
	 
	public String listChannel(ChannelBusStatisForm entity,String o2ourl,String type,String downType,String query,String down){
		if (entity != null) {
			String partnerName = entity.getPartnerName() == null ? "" : entity.getPartnerName();
			String partnerType = entity.getPartnerType() == null ? "" : entity.getPartnerType();
			String province = entity.getProvince() == null ? "" : entity.getProvince();
			String partnerStartDate = entity.getPartnerStartDate()== null ? "" : entity.getPartnerStartDate();
			String partnerEndDate = entity.getPartnerEndDate()== null ? "" : entity.getPartnerEndDate();
			o2ourl = UrlUtil.appendUrl(o2ourl, "partnerName", partnerName);
			o2ourl = UrlUtil.appendUrl(o2ourl, "partnerType", partnerType);
			o2ourl = UrlUtil.appendUrl(o2ourl, "province", province);
			o2ourl = UrlUtil.appendUrl(o2ourl, "partnerStartDate", partnerStartDate);
			o2ourl = UrlUtil.appendUrl(o2ourl, "partnerEndDate", partnerEndDate);
			o2ourl = o2ourl + "&pageIndex=";
		} else {
			o2ourl = o2ourl + "?pageIndex=";
		}
		O2oPlatformRegional q= new O2oPlatformRegional();
		q.setProvinceCode("1");
		q.setProvinceName("总部");
		List<O2oPlatformRegional> proList = provinceNameData.getProvinceNameData();
		proList.add(0,q);
		ViewContext.put("proList", proList);
		ViewContext.put("o2ourl", o2ourl);
		ViewContext.put("vo", entity);
		ViewContext.put("type", type);
		ViewContext.put("downType", downType);
		ViewContext.put("query", query);
		ViewContext.put("down", down);
		return "report/channelList";
	}
	
	public void download(ChannelBusStatisForm entity, HttpServletResponse httpServletResponse,
			HttpServletRequest request,String type) {
		entity.setPageSize(Integer.MAX_VALUE);
		PageResult<Object[]> pageResult = null;
		if((type).equals("0")){
			pageResult = channelBusStatisService.getChannelListForOwnByPage(entity);
		}else{
			pageResult = channelBusStatisService.getChannelListForCommonByPage(entity);
		}
		saveChannelData(pageResult.getData());
		String path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();
		StringBuffer s = new StringBuffer();
		String outPath = s.append(path).append("/channel_for_bill_tem.xls").toString();
		OutputStream os = null;
		try {
			String outfile;
				outfile = "渠道放款数据统计.xls";
			String userAgent = request.getHeader("User-Agent");
			byte[] bytes = userAgent.contains("MSIE") ? outfile.getBytes() : outfile.getBytes("UTF-8");
			outfile = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
			File f = new File(outPath);
			if (!f.exists())
				throw new RuntimeIOException("file not found");
			os = httpServletResponse.getOutputStream();
			httpServletResponse.reset();
			httpServletResponse.setHeader("Content-Disposition", "attachment; filename=" + outfile);
			httpServletResponse.setContentType("application/octet-stream; charset=utf-8");
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			IoUtil.fileToOutputStream(f, byteArrayOutputStream);
			os.write(byteArrayOutputStream.toByteArray());
			os.flush();
		} catch (IOException e) {
			throw new RuntimeIOException(e);
		} finally {
			IoUtil.close(os);
		}
	}

	// 先生成文件然后在读这个文件(省区查询报表)
	public String saveChannelData(List<Object[]> data) {
		// 模板
		String soucreFileName = "/excelTemplate/channel_for_bill.xls";
		// 输出路径
		StringBuffer s = new StringBuffer();
		String path = ProvinceTeamTotalController.class.getResource("/excelTemplate").getPath();

		String outPath = s.append(path).append("/channel_for_bill_tem.xls").toString();

		// 包含的列
		int col = 7;
		// 需要统计的列
		int[] arr = {5,6};
		String title;

		title = "渠道放款数据统计";

		// 开始的表格，行，列
		int sheetIndex = 0;
		int rowIndex = 2;
		int columnIndex = 0;
		BankRowWriteHandler bankRowWriteHandler = new BankRowWriteHandler(data, col, arr, Arrays.asList(title),
				sheetIndex, rowIndex, columnIndex);

		// 生产excel
		write(soucreFileName, outPath, bankRowWriteHandler);

		return path + "/" + outPath;
	}
	
	/*
	 * 生成excel 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
	 */
	public void write(String soucreFileName, String outPath, BankRowWriteHandler bankRowWriteHandler) {
		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath),
					BuildExcelService.class.getResourceAsStream(soucreFileName), bankRowWriteHandler);

		} catch (Exception e) {
			throw new ControllerException(e);
		}
	}

}
